<?php

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
#
#	FILE:			includes/database.php
#	FUNCTION:		Database-related functionality
#	AUTHOR:			Cameron Morrow
#	CREATED:		26/07/2005
#
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


/**
 * establishDatabaseConnection() establishes a connection to the database
*/
function establishDatabaseConnection() {

	# Globals
	global $DATABASE_CONNECTION_ESTABLISHED;
	global $PROJECT_MYSQL_CONNECTION;
	global $PROJECT_DB_CONNECTION;
	global $PROJECT_DB_HOST;
	global $PROJECT_DB_USERNAME;
	global $PROJECT_DB_PASSWORD;
	global $PROJECT_DB;

	# Is Database connected?
	$DATABASE_CONNECTION_ESTABLISHED = false;

	# Attempt to connect to MySQL
	$PROJECT_MYSQL_CONNECTION = @mysql_connect($PROJECT_DB_HOST, $PROJECT_DB_USERNAME, $PROJECT_DB_PASSWORD);

	if (!$PROJECT_MYSQL_CONNECTION) {
		addError("<p>MySQL connection error:<code>" . mysql_error() . "</code></p>", 1);
	}

	# Attempt to connect to Database
	$PROJECT_DB_CONNECTION = @mysql_select_db($PROJECT_DB);

	if (!$PROJECT_DB_CONNECTION) {
		addError("<p>MySQL database connection error:<code>" . mysql_error() . "</code></p>", 1);
	} else {
		$DATABASE_CONNECTION_ESTABLISHED = true;
	}
}

/**
 * establishSQLDatabaseConnection() establishes a connection to the sql server database
*/
function establishSQLDatabaseConnection(){

	global $PROJECT_SQL_SERVER_NAME;

	global $PROJECT_SQL_SERVER_CONNECT_OPTIONS;
	
	global $PROJECT_SQL_SERVER_CONNECTION;
	
	$PROJECT_SQL_SERVER_CONNECTION = @sqlsrv_connect($PROJECT_SQL_SERVER_NAME,$PROJECT_SQL_SERVER_CONNECT_OPTIONS);
	
	if(!$PROJECT_SQL_SERVER_CONNECTION){

		addError("MSSQL database connection fail", 1);
	}
}

/**
 * closeSQLDatabaseConnection() close connection to the sql server database
*/
function closeSQLDatabaseConnection(){

	global $PROJECT_SQL_SERVER_CONNECTION;
	
	if(!sqlsrv_close($PROJECT_SQL_SERVER_CONNECTION)){
	
		addError("<p>MSSQL database connection error:Can`t close connection</p>", 1);
	}
}

/**
*getTruckRecord() call sql server procedures to get truck data
*/
function getTruckRecord($model,$isGroupModel,$cycleDistance,$location,$commodity){

	global $PROJECT_SQL_SERVER_CONNECTION;
	
	if($PROJECT_SQL_SERVER_CONNECTION){

		sqlsrv_configure('WarningsReturnAsErrors', 0);

		$tsql_callSP = "{call [GBIData0001].[dbo].[uspBMKTruckBestPracticeGBI](?,?,?,?,?)}";
		
		$params = array( 
						 array($model,SQLSRV_PARAM_IN),
						 array($isGroupModel,SQLSRV_PARAM_IN),
						 array($cycleDistance,SQLSRV_PARAM_IN),
						 array($location,SQLSRV_PARAM_IN),
						 array($commodity,SQLSRV_PARAM_IN)
					 );

		$stmt = sqlsrv_query($PROJECT_SQL_SERVER_CONNECTION, $tsql_callSP, $params);
			
		if( $stmt === false ){
			
			print_r(sqlsrv_errors());
			
			addError(sqlsrv_errors(),1);
		}

		$result = array();

		while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
				
			$result[] = $row;
		}

		sqlsrv_free_stmt($stmt);
			
		return $result;		
	}
}

/**
*getLoaderRecord() call sql server procedures to get loader data
*/
function getLoaderRecord($model,$isGroupModel,$cum,$location,$commodity){

	global $PROJECT_SQL_SERVER_CONNECTION;
	
	if($PROJECT_SQL_SERVER_CONNECTION){
	
		sqlsrv_configure('WarningsReturnAsErrors', 0);

		$tsql_callSP = "{call [GBIData0001].[dbo].[uspBMKLoaderBestPracticeGBI](?,?,?,?,?)}";
		
		$params = array( 
					array($model,SQLSRV_PARAM_IN),
					array($isGroupModel,SQLSRV_PARAM_IN),
					array($cum,SQLSRV_PARAM_IN),
					array($location,SQLSRV_PARAM_IN),
					array($commodity,SQLSRV_PARAM_IN)
				);

		$stmt = sqlsrv_query($PROJECT_SQL_SERVER_CONNECTION, $tsql_callSP, $params);
		
		if( $stmt === false ){

			addError(sqlsrv_errors(),1);
		}
		
		$result = array();

		while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
			
			$result[] = $row;
		}

		sqlsrv_free_stmt($stmt);
		sqlsrv_close($PROJECT_SQL_SERVER_CONNECTION);
			
		return $result;
	}
		
	return false;
}

/**
 * getRecord attempts to retrieve information from the database.
 *
 * @param sql is the SQL command to run
 * @param index_field (optional) is the field whose value should be used as the index for the returned array
 *
 * @return either an array of information, or FALSE if an error occured, or no data was found
*/
function getRecord($sql = "", $index_field = "") {

	# Get global connection
	global $PROJECT_MYSQL_CONNECTION;
	global $DATABASE_CONNECTION_ESTABLISHED;
	# Create empty array
	$result_array = array();

	# Check connection established
	if ($DATABASE_CONNECTION_ESTABLISHED) {
		# If no sql statement
		if (strlen($sql) == 0) {

			# Add error
			addError("<p>getRecord(): No SQL statement passed.</p>", 3);

		# Contact MySQL database
		} else {

			# If an error occurs with the Query
			if (!$recordset = @mysql_query($sql, $PROJECT_MYSQL_CONNECTION)) {

				# Add Error
				addError("<p>getRecord():<code>" . mysql_error() . "</code></p><p>Query was:<code>" . $sql . "</code></p>", 2);

			} else {

				# Result count
				$row_count = 0;

				# Get data from Array
				while ($row = mysql_fetch_array($recordset)) {

					# Determine row index
					$row_index = ($index_field == "") ? $row_count : $row[$index_field];

					# Create empty array for row
					$result_array[$row_index] = array();

					# Create row, using column names as keys
					foreach ($row as $key => $value) {

						# PHP returns a row with both number and fieldname Keys. Returned array will just have the fieldnames
						if (!is_numeric($key)) {
							$result_array[$row_index][$key] = $value;
						}
					}

					# Increment row count
					$row_count++;
				}

				# Free recordset
				mysql_free_result($recordset);

			}

			# Record
			addSQL("<p>getRecord():<code>" . $sql . "</code></p>" . ((count($result_array) > 0) ? "<p>Returned data</p>" : "<p>Did not return data</p>"));
		}
	} else {

		addError("<p>getRecord(): No database connection established.</p>", 1);
	}

	# Return
	return $result_array;
}

/**
 * executeSQLQuery() executes an SQL query
 *
 * @param sql is the command to execute
 * @param diag is the diagnostic mode
 *
 * @return whether or not the operation was successful
*/
function executeSQLQuery($sql = "", $diag = false) {

	# Globals
	global $PROJECT_MYSQL_CONNECTION;
	global $DATABASE_CONNECTION_ESTABLISHED;
	
	# Return success
	$successful = true;

	# Check database connected
	if ($DATABASE_CONNECTION_ESTABLISHED) {

		# If not diagnostics, execute
		if (!$diag) {;
			if (@!$result = mysql_query($sql, $PROJECT_MYSQL_CONNECTION)) {
				# Add error
				addError("<p>executeSQLQuery():<code>" . mysql_error() . "</code></p><p>Query was:<code>" . $sql . "</code></p>", 2);

				# Fail
				$successful = false;
			}
		}

		# Record
		addSQL("<p>executeSQLQuery:<code>" . $sql . "</code></p><p>Diagnostic mode: " . (($diag) ? "true" : "false") . "</p>");

	} else {

		addError("<p>executeSQLQuery(): No database connection established.</p>", 1);
	}
	
	# Return
	return $successful;
}


/**
 * getLastInsertedKey() gets the last inserted ID if an autoincrement
*/
function getLastInsertedID() {

	# Globals
	global $PROJECT_MYSQL_CONNECTION;

	# Return
	return mysql_insert_id($PROJECT_MYSQL_CONNECTION);
}
?>